package com.qf.dao.impl;

import com.qf.bo.BarBean;
import com.qf.dao.StudentDao;
import com.qf.pojo.Student;
import com.qf.utils.DbUtils;
import com.qf.utils.StringUtil;
import com.qf.vo.BarVo;
import com.qf.vo.PieVo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @date 2022/4/1
 * @desc
 */
public class StudentDaoImpl implements StudentDao {

    QueryRunner queryRunner = new QueryRunner(DbUtils.getDs());


    @Override
    public List<Student> getPageData(int page, int pageSize, Student student) {

        StringBuffer sb = new StringBuffer("select * from t_student ");


        if(StringUtil.isNotEmpty(student.getName())){
            sb.append(" and name like '%"+ student.getName()+"%'");
        }
        if(StringUtil.isNotEmpty(student.getSex())){
            sb.append(" and sex like '%"+ student.getSex()+"%'");
        }
        if(StringUtil.isNotEmpty(student.getAddress())){
            sb.append(" and address like '%"+ student.getAddress()+"%'");
        }
        if(StringUtil.isNotEmpty(student.getPhone())){
            sb.append(" and phone like '%"+ student.getPhone()+"%'");
        }

        sb.append(" limit ?, ?");
        String sql = sb.toString().replaceFirst("and" ,"where");

        System.out.println(sql);

        int start= (page-1) * pageSize;
        List<Student> students = null;
        try {
            students = queryRunner.query(sql, new BeanListHandler<>(Student.class), start, pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return students;
    }

    @Override
    public long getStudentsCount(Student student) {

        StringBuffer sb = new StringBuffer("select count(*) from t_student ");

        if(StringUtil.isNotEmpty(student.getName())){
            sb.append(" and name like '%"+ student.getName()+"%'");
        }
        if(StringUtil.isNotEmpty(student.getSex())){
            sb.append(" and sex like '%"+ student.getSex()+"%'");
        }
        if(StringUtil.isNotEmpty(student.getAddress())){
            sb.append(" and address like '%"+ student.getAddress()+"%'");
        }
        if(StringUtil.isNotEmpty(student.getPhone())){
            sb.append(" and phone like '%"+ student.getPhone()+"%'");
        }

        //查询总记录数
        String sql =  sb.toString().replaceFirst("and" ,"where");
        System.out.println(sql);

        try {
            Long count = queryRunner.query(sql, new ScalarHandler<Long>());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public int deleteStudentById(int id) {

        String sql="delete from t_student where id = ?";
        int update = 0;
        try {
            update = queryRunner.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public int updateStudentById(Student student) {
        String sql="update t_student set name = ? ,sex=? , birthday =? ,phone =? ,address = ? ,hobby=? where id = ?";

        int update = 0;
        try {
            update = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getPhone(), student.getAddress(), student.getHobby(), student.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public int addStudent(Student student) {
        String sql = "insert into t_student (id,name,sex,birthday,phone,address,hobby) values (null,?,?,?,?,?,?)";

        int update = 0;
        try {
            update = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getPhone(), student.getAddress(), student.getHobby());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public BarVo showSexBar() {

        String sql="SELECT COUNT(*) as num,sex FROM t_student GROUP BY sex;";

        BarVo barVo = new BarVo();
        List<String> sexList = new ArrayList<>();
        List<Integer> sexNum = new ArrayList<>();

        try {
            List<BarBean> barBeans = queryRunner.query(sql, new BeanListHandler<>(BarBean.class));

            sexList = barBeans.stream().map(BarBean::getSex).collect(Collectors.toList());

            sexNum  = barBeans.stream().map(BarBean::getNum).collect(Collectors.toList());

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        barVo.setSexList(sexList);
        barVo.setSexCount(sexNum);
        return barVo;
    }

    @Override
    public List<PieVo> showSexPie() {
        String sql="SELECT COUNT(*) as value,sex as name FROM t_student GROUP BY sex;";

        List<PieVo> pieVos = null;
        try {
            pieVos = queryRunner.query(sql, new BeanListHandler<>(PieVo.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return pieVos;
    }
}
